<?php

class Baza extends Controller {

    function Baza() {
        parent::Controller();
        $this->load->model('model_data');
        $this->load->model('model_baza');
        $this->load->config("const_config");
        $this->load->model("model_data");
        //verificare sesiune
        if (!$this->usersession->userLoggedIn()) {
            $aResponse = array("error" => true, "description" => "Sesiunea a expirat", "type" => "nosession");
            die(json_encode($aResponse));
        }
    }

    function bringBaza() {

        $aPost = $aData = array();
        copyPost($aPost);

        if (isset($aPost["sort"]) && $aPost["sort"] != "") {
            switch ($aPost["sort"]) {
                case 'position_id':
                    $aPost["sort"] = "persons.position_id";
                    break;
                case 'position_sub_name':
                    $aPost["sort"] = "P.name";
                    break;
                case 'client_id':
                    $aPost["sort"] = "persons.client_id";
                    break;
            }
        }

        //creare WHERE
        $aWhere = $this->model_baza->getWhere($aPost);

        //cautare simpla
        if (!isset($aPost["custom"]) || (isset($aPost["custom"]) && $aPost["custom"] != "dubluri")) {
            $aData = $this->model_baza->bringBaza($aPost, $aWhere);
        }
        //cautare simpla
        if (isset($aPost["custom"]) && $aPost["custom"] == "dubluri") {
            $aData = $this->model_baza->bringBazaDubluri($aPost, $aWhere);
        }

        echo json_encode($aData);
    }

    /**
     * Functie care transforma id-urile din campul clients_id in text
     */
    function transfClient($sData, $aClient) {

        $aData = explode("=", $sData);
        $aFinal = array();
        foreach ($aData as $iClientId) {
            $aFinal[] = $aClient[$iClientId];
        }
        return implode(",", $aFinal);
    }

    /**
     * Functie de export baza de date
     * @return
     */
    function export() {

        $aPost = array();
        copyPost($aPost);
        $aPost["export"] = 1;

        //verificare ca la export sa se selecteze macar un filtru ( probleme de performanta)
        $aCheck = array(
            "name", "fname", "sex", "age", "position_id", "position_sub_id", "division_id", "priority", "available",
            "income", "user", "email", "unsubscribed", "createdfrom", "modfrom", "end", "custom", "company", "letter",
            "domain_id", "domain_sub_id", "address", "city_id", "county_id", "employees", "client_id", "client_id_not",
            "turnover1", "turnover2");

        //verificare daca sunt ftoate filtrele nule
        $bExport = FALSE;
        foreach ($aCheck as $aCheckRec) {
            if ($aPost[$aCheckRec] != "") {
                $bExport = TRUE;
            }
        }
        if (!$bExport) {
            print_r("Selectati macar un filtru");
            return;
        }
		

        //creare string WHERE
        $aWhere = $this->model_baza->getWhere($aPost);

        $this->db->query("SET NAMES 'utf8'");
        if (!isset($aPost["custom"]) || (isset($aPost["custom"]) && $aPost["custom"] != "dubluri")) {
            $result = $this->model_baza->bringBaza($aPost, $aWhere);
        }
        //cautare simpla
        if (isset($aPost["custom"]) && $aPost["custom"] == "dubluri") {
            $result = $this->model_baza->bringBazaDubluri($aPost, $aWhere);
        }

        //creare array cu clienti
        $data["table"] = "client";
        $aClientsFromTable = $this->model_data->bringObject($data);
        $aClients = array();
        foreach ($aClientsFromTable as $client) {
            $aClients[$client["id"]] = $client["name"];
        }

        //creare array cu useri
        $data["table"] = "user";
        $aUsersFromTable = $this->model_data->bringObject($data);
        $aUsers = array();
        foreach ($aUsersFromTable as $user) {
            $aUsers[$user["id"]] = $user["name"];
        }


        $customs["age"] = $this->config->item("ageContact");
        $customs["income"] = $this->config->item("incomeContact");
        $customs["employees"] = $this->config->item("employeeContact");
        $customs["available"] = $this->config->item("availableContact");
        $customs["priority"] = $this->config->item("priorityContact");
        $customs["sex"] = $this->config->item("sexContact");
        //$customs["turnover"] = $this->config->item("turnoverContact");

        $litere_er = array('"', '<', '>');
        $litere_sol = array(",", " ", " ");

        $this->load->library('xmlexcel');

        header("Content-type: application/vnd.ms-excel;charset=utf-8");
        header("Expires: 0");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Content-disposition:  attachment; filename=raport.xls ");
        $newexcel = new Xmlexcel;

        $newexcel->write_header();
        $newexcel->write_document_properties('MTP');
        $newexcel->write_styles();

        $newexcel->start_worksheet();
        $newexcel->write_row('
	<Column ss:Width="200"/>
	<Column ss:Width="100"/>
	<Column ss:Width="150"/> 
	<Column ss:Width="60"/>
	<Column ss:Width="250"/>
	<Column ss:Width="250"/>
	<Column ss:Width="150"/> 
	<Column ss:Width="100"/>
	<Column ss:Width="100"/>
	<Column ss:Width="250"/>
	<Column ss:Width="100"/>
	<Column ss:Width="100"/>
	<Column ss:Width="100"/>
	<Column ss:Width="150"/>
	<Column ss:Width="150"/> 
	<Column ss:Width="150"/> 
	<Column ss:Width="150"/>
	<Column ss:Width="100"/>
	<Column ss:Width="100"/>
	<Column ss:Width="100"/>
	<Column ss:Width="100"/>
	<Column ss:Width="100"/>
	<Column ss:Width="100"/>
	<Column ss:Width="100"/>
	<Column ss:Width="100"/>
	<Column ss:Width="100"/>
	<Column ss:Width="200"/>
	<Column ss:Width="200"/>
	<Column ss:Width="200"/>
	<Column ss:Width="200"/>
	<Column ss:Width="200"/>
	<Column ss:Width="200"/>
	<Column ss:Width="200"/>
	<Column ss:Width="200"/>
	<Column ss:Width="200"/>
	<Column ss:Width="100"/>
	<Column ss:Width="100"/>
    ');


        $newexcel->write_row('
    <Row>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Companie</Data></Cell>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Nume</Data></Cell>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Prenume</Data></Cell>    
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Titlu</Data></Cell>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Domeniu</Data></Cell>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Subdomeniu</Data></Cell>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Functie</Data></Cell>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Subfunctie</Data></Cell>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Departament</Data></Cell>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Adresa</Data></Cell>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Cod Postal</Data></Cell>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Oras</Data></Cell>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Judet</Data></Cell>    
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Telefon</Data></Cell>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Mobil</Data></Cell>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Email</Data></Cell>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Email2</Data></Cell>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Adresa Web</Data></Cell>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Zi nastere</Data></Cell>    
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Campanie</Data></Cell>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Grupa Varsta</Data></Cell>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Grupa Venit</Data></Cell>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Identificat</Data></Cell>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Prioritate</Data></Cell>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Cifra Afaceri 2012</Data></Cell>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Nr Angajati</Data></Cell>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Observatii</Data></Cell>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Observatii Companie</Data></Cell>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Cod Inregistrare</Data></Cell>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Cifra Afaceri 2010</Data></Cell>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Cifra Afaceri 2011</Data></Cell>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Ultima modificare</Data></Cell>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Data ultima modificare</Data></Cell>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Penultima modificare</Data></Cell>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Data penultima modificare</Data></Cell>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Creat de</Data></Cell>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Data creare</Data></Cell>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Dezabonat</Data></Cell>
	<Cell ss:StyleID="headerStyle"><Data ss:Type="String">ID Baza de date</Data></Cell>


     </Row>');


        foreach ($result['DS_PERSONS'] as $key => $row) {

            $newexcel->write_row('<Row>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . strUpper(str_replace($litere_er, $litere_sol, $row["company"])) . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . strTitle(str_replace($litere_er, $litere_sol, $row["lname"])) . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . strTitle(str_replace($litere_er, $litere_sol, $row["fname"])) . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . $customs["sex"][$row["sex"]] . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . str_replace($litere_er, $litere_sol, $row["domain_name"]) . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . str_replace($litere_er, $litere_sol, $row["subdomain_name"]) . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . str_replace($litere_er, $litere_sol, $row["position_name"]) . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . str_replace($litere_er, $litere_sol, $row["subposition_name"]) . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . strTitle(str_replace($litere_er, $litere_sol, $row["division_name"])) . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . str_replace($litere_er, $litere_sol, $row["address"]) . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . str_replace($litere_er, $litere_sol, $row["address2"]) . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . strTitle(str_replace($litere_er, $litere_sol, $row["city_name"])) . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . strTitle(str_replace($litere_er, $litere_sol, $row["county_name"])) . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . str_replace($litere_er, $litere_sol, $row["phone"]) . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . str_replace($litere_er, $litere_sol, $row["mobil"]) . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . str_replace($litere_er, $litere_sol, $row["email"]) . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . str_replace($litere_er, $litere_sol, $row["email2"]) . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . str_replace($litere_er, $litere_sol, $row["web"]) . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . str_replace($litere_er, $litere_sol, $row["birth"]) . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . $row["client_name"] . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . $customs["age"][$row["age"]] . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . $customs["income"][$row["income"]] . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . $customs["available"][$row["available"]] . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . $customs["priority"][$row["priority"]] . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . $row["turnover"] . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . $customs["employees"][$row["employees"]] . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . $row["obs"] . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . $row["obs_company"] . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . $row["cui"] . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . $row["turnover2010"] . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' .$row["turnover2011"] . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . $aUsers[$row["modify_by"]] . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . substr($row["date_modified"], 0, 10) . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . $aUsers[$row["modify_by2"]] . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . substr($row["date_modified2"], 0, 10) . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . $aUsers[$row["create_by"]] . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . substr($row["date_created"], 0, 10) . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . $customs["available"][$row["unsubscribed"]] . '</Data></Cell>
	    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . $row["person_id"] . '</Data></Cell>


	    </Row>');

            if (isset($result['DS_PERSONS'][$key])) {
                unset($result['DS_PERSONS'][$key]);
            }
        }

        $newexcel->end_worksheet();
        $newexcel->write_footer();
        print $newexcel->output();
        exit;
    }
}